﻿/// <summary>
// DBLinq.MySqlProviders - .NET Providers for MySql using the framework DBLinq under MIT license, author Jiri George Moudry
// http://linq.to/db
// and the CodeFx.MySqlProviders under GNU licence, author Laurent Morisseau
// http://www.codeplex.com/CodeFxMySqlProvider
//
// This program is free software; you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
//
// Laurent Morisseau
/// </summary>
/// <author><a href="mailto:laurent.morisseau@gmail.com">Laurent Morisseau</a></author>
/// 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Configuration;

namespace DBLinq.MySqlProviders.TestProject
{
	internal static class TestHelper
	{
		internal const string ApplicationNameTest = "ApplicationNameTest";
        internal const string UserName = "test";
        internal const string RoleName = "test";
        internal const string Email = "test";
        internal const string MySqlMembershipProvider = "MySqlMembershipProvider";
        internal const string connectionstring = "server=localhost;user id=root; password=sa; database=mysqlproviders; pooling=true;";

        #region Connection
        internal static MySqlConnection Open()
		{
			try
			{

                MySqlConnection conn = new MySqlConnection(connectionstring);
				conn.Open ();
				return conn;
			}
			catch ( Exception ex )
			{
				System.Diagnostics.Trace.WriteLine ( ex.Message );
				throw;
			}
		}


		internal static void Close( MySqlConnection conn )
		{
			try
			{
				if ( conn != null )
					conn.Close ();
			}
			catch ( Exception ex )
			{
				System.Diagnostics.Trace.WriteLine ( ex.Message );
				throw;
			}
        }
        #endregion

        #region Application
        internal static uint InsertApplicationTest( MySqlConnection conn )
		{
			try
			{
				MySqlCommand cmd = new MySqlCommand ( string.Format ( "INSERT INTO AspNet_Applications (ApplicationName) values ('{0}')", ApplicationNameTest ), conn );
				cmd.ExecuteNonQuery ();

                cmd = new MySqlCommand("select ApplicationId from AspNet_Applications order by ApplicationId desc limit 0,1",conn);
                object appid = cmd.ExecuteScalar();
                uint uappid = 0;
                if (uint.TryParse(appid.ToString(), out uappid))
                    return uappid;
                else
                    return 0;
			}
			catch ( Exception ex )
			{
				System.Diagnostics.Trace.WriteLine ( ex.Message );
				throw;
			}
		}

		internal static void DeleteApplicationTest( MySqlConnection conn )
		{
			try
			{
				MySqlCommand cmd = new MySqlCommand ( string.Format ( "Delete FROM AspNet_Applications WHERE applicationName='{0}'", ApplicationNameTest ), conn );
				cmd.ExecuteNonQuery ();
			}
			catch ( Exception ex )
			{
				System.Diagnostics.Trace.WriteLine ( ex.Message );
				throw;
			}
        }
        #endregion

        #region Member
        internal static uint InsertMemberTest( MySqlConnection conn, uint appid )
		{
			try
            {
                MySqlCommand cmd = new MySqlCommand("insert into aspnet_users (userid, applicationid, username, IsAnonymous, LastActivityDate) values (0, ?appid, ?username, 0, Now())", conn);
                cmd.Parameters.Add(new MySqlParameter("?appid", appid));
                cmd.Parameters.Add(new MySqlParameter("?username", UserName));
                cmd.ExecuteNonQuery();

                cmd = new MySqlCommand("select userid from aspnet_users order by userid desc limit 0,1",conn);
                object userid = cmd.ExecuteScalar();

                cmd = new MySqlCommand("insert into AspNet_Membership (userid, applicationid, password, passwordformat, passwordsalt, LastLoginDate, Email) values (?userid, ?appid, 'password', 0, 'password', Now(), ?email)", conn);
                cmd.Parameters.Add(new MySqlParameter("?userid", userid));
                cmd.Parameters.Add(new MySqlParameter("?appid", appid));
                cmd.Parameters.Add(new MySqlParameter("?email", Email));
                cmd.ExecuteNonQuery ();

                return (uint)userid;
			}
			catch ( Exception ex )
			{
				System.Diagnostics.Trace.WriteLine ( ex.Message );
				throw;
			}
		}
        
        internal static bool ExistMemberTest(MySqlConnection conn, uint appid)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand("select userid from aspnet_users order by userid desc limit 0,1", conn);
                object userid = cmd.ExecuteScalar();

                return userid != null;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
                throw;
            }
        }

        internal static void DeleteMemberTest(MySqlConnection conn)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand("Delete AspNet_Membership.* from AspNet_Membership,  aspnet_users where aspnet_users.userid = AspNet_Membership.userid and aspnet_users.username=?username", conn);
                cmd.Parameters.Add(new MySqlParameter("?username", UserName));
                cmd.ExecuteNonQuery();

                cmd = new MySqlCommand("Delete FROM aspnet_users WHERE username=?username", conn);
                cmd.Parameters.Add(new MySqlParameter("?username", UserName));
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
                throw;
            }
        }

		internal static void DeleteMemberTest( MySqlConnection conn, string userid )
		{
			try
			{
                MySqlCommand cmd = new MySqlCommand("Delete FROM AspNet_Membership WHERE userid=?userid", conn);
                cmd.Parameters.Add(new MySqlParameter ("?userid", userid));
				cmd.ExecuteNonQuery ();
            
                cmd = new MySqlCommand("Delete FROM aspnet_users WHERE userid=?userid", conn);
                cmd.Parameters.Add(new MySqlParameter("?userid", userid));
                cmd.ExecuteNonQuery();
            }
			catch ( Exception ex )
			{
				System.Diagnostics.Trace.WriteLine ( ex.Message );
				throw;
			}
        }

        #endregion

        #region Role
       internal static void DeleteRoleTest(MySqlConnection conn, uint appid)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand("Delete  from aspnet_roles where applicationid = ?applicationid", conn);
                cmd.Parameters.Add(new MySqlParameter("?applicationid", appid));
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
                throw;
            }
        }

        internal static uint InsertRole(MySqlConnection conn, uint appid)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand("insert into aspnet_roles (ApplicationId, rolename, description) values (?appid, ?rolename, ?description)", conn);
                cmd.Parameters.Add(new MySqlParameter("?appid", appid));
                cmd.Parameters.Add(new MySqlParameter("?rolename", RoleName));
                cmd.Parameters.Add(new MySqlParameter("?description", "test"));
                cmd.ExecuteNonQuery();

                cmd = new MySqlCommand("select roleid from aspnet_roles order by roleid desc limit 0,1", conn);
                object roleid = cmd.ExecuteScalar();
                return (uint)roleid;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
                throw;
            }
        }

        #endregion

        #region User
        internal static uint InsertUserTest(MySqlConnection conn, uint appid)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand("insert into aspnet_users (userid, applicationid, username, IsAnonymous, LastActivityDate) values (0, ?appid, ?username, 0, Now())", conn);
                cmd.Parameters.Add(new MySqlParameter("?appid", appid));
                cmd.Parameters.Add(new MySqlParameter("?username", UserName));
                cmd.ExecuteNonQuery();

                cmd = new MySqlCommand("select userid from aspnet_users order by userid desc limit 0,1", conn);
                object userid = cmd.ExecuteScalar();

                return (uint)userid;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
                throw;
            }
        }

        internal static void DeleteUserTest(MySqlConnection conn)
        {
            try
            {

                MySqlCommand cmd = new MySqlCommand("Delete FROM aspnet_users WHERE username=?username", conn);
                cmd.Parameters.Add(new MySqlParameter("?username", UserName));
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
                throw;
            }
        }

        internal static void DeleteUserTest(MySqlConnection conn, uint userid)
        {
            try
            {

                MySqlCommand cmd = new MySqlCommand("Delete FROM aspnet_users WHERE userid=?userid", conn);
                cmd.Parameters.Add(new MySqlParameter("?userid", userid));
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
                throw;
            }
        }
  
        #endregion

        #region User In Role
        internal static void InsertUserInRole(MySqlConnection conn, uint userid, uint roleid)
        {
            try
            {


                MySqlCommand cmd = new MySqlCommand("insert into aspnet_usersinroles (userid, roleid) values (?userid, ?roleid)", conn);
                cmd.Parameters.Add(new MySqlParameter("?userid", userid));
                cmd.Parameters.Add(new MySqlParameter("?roleid", roleid));
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
                throw;
            }
        }
        
        internal static void DeleteUserinRole(MySqlConnection conn, uint userid)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand("Delete  from aspnet_usersinroles where userid = ?userid", conn);
                cmd.Parameters.Add(new MySqlParameter("?userid", userid));
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
                throw;
            }
        }
        #endregion
    }
}
